import os

import chardet
import mysql
import pandas as pd
import re
import mysql.connector



def test():
    df = pd.read_excel("C:/Users/23226/Desktop/xcx.xlsx")
    # 获取 O 列的数据，排除第一行
    data_list = df['电子卡号'].tolist()
    equipment_name_list = df['设备名称'].tolist()
    # 通过正则表达式筛选出想要的数据
    content = extract_data(file_path="C:/Users/23226/Desktop/222.sql")
    two_data = []
    # 读取订单金额
    data_list_price = df['订单金额'].tolist()
    for key in data_list:
        a = find_value_by_key(key, content)
        two_data.append(a)
    # 将订单金额，会员码，电子卡号写进表格
    file_path = "C:/Users/23226/Desktop/re.xlsx"
    write_to_excel(data_list, two_data, data_list_price, file_path)

    # 读取re表格并获取所有的电子卡号
    df1 = pd.read_excel("C:/Users/23226/Desktop/re.xlsx")
    card_on = df1['电子卡编号'].tolist()
    card_price = df1['扣款金额'].tolist()
    # 调用数据库，查询数据
    phone_list, name_list = sql_use(card_on)
    # 写入数据
    path_biaoge = "C:/Users/23226/Desktop/555.xlsx"
    update_excel_with_phone_numbers(path_biaoge, phone_list, name_list, card_price, equipment_name_list)


def update_excel_with_phone_numbers(file_path, phone_list, name_list, card_price, equipment_name_list):
    # 确保文件路径是字符串类型，并且文件存在
    if not isinstance(file_path, str) or not os.path.isfile(file_path):
        raise ValueError("Invalid file path or buffer object type")

    # 尝试读取现有的 Excel 文件
    try:
        df = pd.read_excel(file_path, engine='openpyxl')
    except PermissionError:
        print(
            f"Permission denied: Cannot access the file at {file_path}. Make sure the file is not open in another "
            f"program and you have the necessary permissions.")
        return
    except Exception as e:
        print(f"Failed to read Excel file: {e}")
        return

    # 确保“手机号”和“扣款金额”列存在
    if '手机号' in df.columns and '扣款金额' in df.columns and '姓名' in df.columns and '设备名称' in df.columns:
        # 获取现有的行数
        existing_rows = len(df)

        # 将手机号和扣款金额列表转换为 DataFrame
        new_data = pd.DataFrame({
            '手机号': pd.Series(phone_list),
            '扣款金额': pd.Series(card_price),
            '姓名': pd.Series(name_list),
            '设备名称': pd.Series(equipment_name_list)
        })

        # 将新数据追加到现有 DataFrame 中
        df = pd.concat([df, new_data], ignore_index=True)

        # 尝试保存更新后的 Excel 文件
        try:
            df.to_excel(file_path, index=False, engine='openpyxl')
            print(f"Phone numbers and amounts have been appended to the columns '手机号' and '扣款金额' in {file_path}")
        except PermissionError:
            print(f"Permission denied: Cannot save the file at {file_path}. Make sure you have write permissions.")
        except Exception as e:
            print(f"Failed to save Excel file: {e}")
    else:
        print("The columns '手机号' and '扣款金额' do not exist in the Excel file.")


def sql_use(card_on):
    phone_list = []
    name_list = []
    # 连接到MySQL数据库
    conn = mysql.connector.connect(
        host='192.168.1.30',  # 替换为你的数据库主机名
        user='keyijie',  # 替换为你的数据库用户名
        password='19kyj20St08',  # 替换为你的数据库密码
        database='rcm_canteen'  # 替换为你的数据库名称
    )
    # 创建一个游标对象
    cursor = conn.cursor()
    for i in card_on:
        card_id_query = f"""
        SELECT m.card_id, m.member_id, ms.phone_number, ms.name
        FROM meal_card m
        JOIN membership ms ON m.member_id = ms.id
        WHERE m.card_id = '{i}';
        """
        cursor.execute(card_id_query)
        result = cursor.fetchone()
        if result:
            card_id, member_id, phone_number, name = result
            phone_list.append(phone_number)
            name_list.append(name)
    return phone_list, name_list


def write_to_excel(data_list, two_data, data_list_price, file_path):
    # 创建一个字典，将三个列表映射到列名
    data = {
        '二维码': data_list,
        '电子卡编号': two_data,
        '扣款金额': data_list_price
    }
    # 将字典转换为 DataFrame
    df = pd.DataFrame(data)

    # 将 DataFrame 写入 Excel 文件
    df.to_excel(file_path, index=False)


def find_value_by_key(key, content):
    # 创建 @5 到 @2 的映射
    lookup = {item['@5']: item['@2'] for item in content}

    # 查找 target_key 对应的 @2 值
    return lookup.get(key, None)


def detect_encoding(file_path):
    with open(file_path, 'rb') as file:
        raw_data = file.read(10000)  # 读取文件的一部分数据以检测编码
    result = chardet.detect(raw_data)
    return result['encoding']


def extract_data(file_path):
    encoding = detect_encoding(file_path)
    data_list = []
    current_data = {'@2': None, '@5': None}

    with open(file_path, 'r', encoding=encoding) as file:
        for line in file:
            match = re.search(r"@2='([^']*)'", line)
            if match:
                current_data['@2'] = match.group(1)
            match = re.search(r"@5='([^']*)'", line)
            if match:
                current_data['@5'] = match.group(1)
            # data_list.append(current_data.copy())
            # When the line contains 'COMMIT', add current_data to data_list
            if 'COMMIT' in line:
                if current_data['@2'] is not None and current_data['@5'] is not None:
                    data_list.append(current_data.copy())
                # Reset for the next transaction
                current_data = {'@2': None, '@5': None}

    return data_list


if __name__ == '__main__':
    test()
